Administrative privileges must be assigned to database accounts via database roles.

Overview

Finding ID	Version	Rule ID	IA Controls	Severity
V-41397	SQL2-00-009500	SV-53922r1_rule		Medium

Description
SQL Server must employ the concept of least privilege for specific duties and information systems (including specific functions, ports, protocols, and services). The concept of least privilege is also applied to information system processes, ensuring that the processes operate at privilege levels no higher than necessary to accomplish required organizational missions and/or functions. Organizations consider the creation of additional processes, roles, and information system accounts as necessary to achieve least privilege. Organizations also apply least privilege concepts to the design, development, implementation, and operations of information systems. Privileges granted outside the role of the application user job function are more likely to go unmanaged or without oversight for authorization. Maintenance of privileges using roles defined for discrete job functions offers improved oversight of application user privilege assignments and helps to protect against unauthorized privilege assignment. SQL Server access to administrative privileges can be made by membership or ownership to the administrative roles. These administrative roles must not be assigned directly to administrative user accounts. If administrative user accounts have direct access to administrative roles, this access must be removed.

Description

SQL Server must employ the concept of least privilege for specific duties and information systems (including specific functions, ports, protocols, and services). The concept of least privilege is also applied to information system processes, ensuring that the processes operate at privilege levels no higher than necessary to accomplish required organizational missions and/or functions. Organizations consider the creation of additional processes, roles, and information system accounts as necessary to achieve least privilege. Organizations also apply least privilege concepts to the design, development, implementation, and operations of information systems. Privileges granted outside the role of the application user job function are more likely to go unmanaged or without oversight for authorization. Maintenance of privileges using roles defined for discrete job functions offers improved oversight of application user privilege assignments and helps to protect against unauthorized privilege assignment. SQL Server access to administrative privileges can be made by membership or ownership to the administrative roles. These administrative roles must not be assigned directly to administrative user accounts. If administrative user accounts have direct access to administrative roles, this access must be removed.

STIG	Date
Microsoft SQL Server 2012 Database Security Technical Implementation Guide	2014-06-23

Details

Check Text ( C-47934r2_chk )
Check administrative accounts for direct 'Database role membership' to administration roles like: 'db_accessadmin', 'db_owner', etc. Navigate to SQL Server Management Studio >> Object Explorer >> <'SQL Server name'> >> Security >> Logins >> right click <'administrator account name'> >> Properties >> User Mapping >> <'highlight database'> >> check 'Database role membership' each database. If any administration accounts have a direct privilege to any 'Database role membership' that is part of the SQL Server system, this is a finding. Check administrative accounts for direct 'Server roles:' membership like: 'db_accessadmin', 'db_owner', etc... Navigate to SQL Server Management Studio >> Object Explorer >> <'SQL Server name'> >> Security >> Logins >> right click <'administrator account name'> >> Properties >> Server Roles. If any administration accounts have direct access to any 'Server roles' privilege that is part of the SQL Server system, this is a finding.

Check Text ( C-47934r2_chk )

Check administrative accounts for direct 'Database role membership' to administration roles like: 'db_accessadmin', 'db_owner', etc.

Navigate to SQL Server Management Studio >> Object Explorer >> <'SQL Server name'> >> Security >> Logins >> right click <'administrator account name'> >> Properties >> User Mapping >> <'highlight database'> >> check 'Database role membership' each database.

If any administration accounts have a direct privilege to any 'Database role membership' that is part of the SQL Server system, this is a finding.

Check administrative accounts for direct 'Server roles:' membership like: 'db_accessadmin', 'db_owner', etc...

Navigate to SQL Server Management Studio >> Object Explorer >> <'SQL Server name'> >> Security >> Logins >> right click <'administrator account name'> >> Properties >> Server Roles.

If any administration accounts have direct access to any 'Server roles' privilege that is part of the SQL Server system, this is a finding.

Fix Text (F-46822r2_fix)
Navigate to SQL Server Management Studio >> Object Explorer >> <'SQL Server name'> >> Security >> Logins >> right click <'administrator account name'> >> Properties >> User Mapping >> <'highlight the database'> >> check 'Database role membership' each database. Remove 'Database role membership' by clicking the appropriate check box. Navigate to SQL Server Management Studio >> Object Explorer >> <'SQL Server name'> >> Security >> Logins >> right click <'administrator account name'> >> Properties >> Server Roles. Remove 'Server roles' by clicking the appropriate check box.

Fix Text (F-46822r2_fix)

Navigate to SQL Server Management Studio >> Object Explorer >> <'SQL Server name'> >> Security >> Logins >> right click <'administrator account name'> >> Properties >> User Mapping >> <'highlight the database'> >> check 'Database role membership' each database.

Remove 'Database role membership' by clicking the appropriate check box.

Navigate to SQL Server Management Studio >> Object Explorer >> <'SQL Server name'> >> Security >> Logins >> right click <'administrator account name'> >> Properties >> Server Roles.

Remove 'Server roles' by clicking the appropriate check box.